/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.moedik.model;

import com.moedik.classes.City;
import com.moedik.classes.Pool;
import com.moedik.classes.ShippingQos;
import com.moedik.classes.ShippingService;
import com.moedik.classes.Shipping;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author SAMMY
 */
public class ShippingModel extends Model {

    public ShippingModel() {
        super();
    }

    public ArrayList<ShippingService> getServices() {
        ArrayList<ShippingService> services = new ArrayList<ShippingService>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT `shipping_services`.`id` AS `id`, `origin`.`city` AS `origin_city`, `origin`.`name` AS `origin_pool`, `origin`.`address` AS `origin_address`,`origin`.`phone` AS `origin_phone`,`destination`.`city` AS `destination_city`,`destination`.`name` AS `destination_pool`, `destination`.`address` AS `destination_address`, `destination`.`phone` AS `destination_phone`,`shipping_qos`.`name` AS `qos`,`shipping_services`.`rate` AS `rate` FROM `shipping_services`JOIN(SELECT `shipping_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `shipping_services` JOIN `pools` JOIN `cities` WHERE `shipping_services`.`origin_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `origin` JOIN (SELECT `shipping_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city`FROM`shipping_services` JOIN `pools` JOIN `cities` WHERE `shipping_services`.`destination_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `destination` JOIN `shipping_qos` WHERE `origin`.`id` = `shipping_services`.`id` AND `destination`.`id` = `shipping_services`.`id` AND `shipping_services`.`qos_id` = `shipping_qos`.`id` ORDER BY `origin`.`city`, `destination`.`city`, `shipping_services`.`rate`";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                ShippingService shippingService = new ShippingService(rs.getInt("id"), rs.getString("origin_city"), rs.getString("origin_pool"), rs.getString("origin_address"), rs.getString("origin_phone"), rs.getString("destination_city"), rs.getString("destination_pool"), rs.getString("destination_address"), rs.getString("destination_phone"), rs.getString("qos"), rs.getInt("rate"));
                services.add(shippingService);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return services;
    }

    public int register(int userId, int serviceId, int weight, String recipient, String phone) {
        int trackingId = -2;
        PreparedStatement stmt = null;

        try {
            Calendar cal = Calendar.getInstance();
            String date = cal.get(Calendar.YEAR)+"-"+cal.get(Calendar.MONTH)+"-"+cal.get(Calendar.DATE);
            String query = "INSERT INTO `shippings` (`id`, `user_id`, `service_id`, `weight`, `recipient`, `phone`, `status_id`) VALUES (NULL, ?, ?, ?, ?, ?, ?, DEFAULT)";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.setInt(1, userId);
            stmt.setInt(2, serviceId);
            stmt.setInt(3, weight);
            stmt.setString(4, recipient);
            stmt.setString(5, phone);
            stmt.setString(6, date);

            int affectedRow = stmt.executeUpdate();

            ResultSet rs = stmt.getGeneratedKeys();

            if (rs != null && rs.next()) {
                trackingId = rs.getInt(1);
            }
            this.lm.log("pengiriman layanan "+serviceId+" telah ditambahkan");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return trackingId;
    }
    
    public ArrayList<Shipping> getAll() {
        ArrayList<Shipping> shippings = new ArrayList<Shipping>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT `shippings`.`id` AS `id`, `users`.`id` AS `user_id`, `users`.`name` AS `user_name`, `shippings`.`service_id` AS `service_id`, `services`.`origin_city` AS `origin_city`, `services`.`origin_pool` AS `origin_pool`, `services`.`origin_address` AS `origin_address`, `services`.`origin_phone` AS `origin_phone`, `services`.`destination_city` AS `destination_city`, `services`.`destination_pool` AS `destination_pool`, `services`.`destination_address` AS `destination_address`, `services`.`destination_phone` AS `destination_phone`, `services`.`qos` AS `qos`, `services`.`rate` AS `rate`, `shippings`.`weight` AS `weight`, `shippings`.`weight` * `services`.`rate` AS `cost`, `shippings`.`recipient` AS `recipient`, `shippings`.`phone` AS `phone`, `shipping_status`.`name` AS `status` FROM `shippings` JOIN `users` JOIN `shipping_status` JOIN (SELECT `shipping_services`.`id` AS `id`, `origin`.`city` AS `origin_city`, `origin`.`name` AS `origin_pool`, `origin`.`address` AS `origin_address`,`origin`.`phone` AS `origin_phone`,`destination`.`city` AS `destination_city`,`destination`.`name` AS `destination_pool`, `destination`.`address` AS `destination_address`, `destination`.`phone` AS `destination_phone`,`shipping_qos`.`name` AS `qos`,`shipping_services`.`rate` AS `rate` FROM `shipping_services`JOIN(SELECT `shipping_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `shipping_services` JOIN `pools` JOIN `cities` WHERE `shipping_services`.`origin_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `origin` JOIN (SELECT `shipping_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city`FROM`shipping_services` JOIN `pools` JOIN `cities` WHERE `shipping_services`.`destination_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `destination` JOIN `shipping_qos` WHERE `origin`.`id` = `shipping_services`.`id` AND `destination`.`id` = `shipping_services`.`id` AND `shipping_services`.`qos_id` = `shipping_qos`.`id` ORDER BY `origin`.`city`, `destination`.`city`, `shipping_services`.`rate`) AS `services` WHERE `shippings`.`user_id` = `users`.`id` AND `shippings`.`service_id` = `services`.`id` AND `shippings`.`status_id` = `shipping_status`.`id` AND `shippings`.`service_id` = `services`.`id`";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Shipping shipping = new Shipping(rs.getInt("id"), rs.getInt("user_id"), rs.getString("user_name"), rs.getInt("service_id"), rs.getString("origin_city"), rs.getString("origin_pool"), rs.getString("origin_address"), rs.getString("origin_phone"), rs.getString("destination_city"), rs.getString("destination_pool"), rs.getString("destination_address"), rs.getString("destination_phone"), rs.getString("qos"), rs.getInt("rate"), rs.getInt("weight"), rs.getInt("cost"), rs.getString("recipient"), rs.getString("phone"), rs.getString("status"));
                shippings.add(shipping);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return shippings;
    }
    
    public int delete(int id) {
        int rowCount = 0;
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "DELETE FROM `shippings` WHERE `shippings`.`id` = " + id;
            rowCount = stmt.executeUpdate(query);
            this.lm.log("pengiriman "+id+" telah dihapus");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return rowCount;
    }

    public ArrayList<Pool> getPoolServices(int cityId) {
        ArrayList<Pool> pools = new ArrayList<Pool>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT DISTINCT pools.id AS id, pools.name AS name, pools.address AS address, pools.phone AS phone FROM pools,travel_services WHERE pools.city_id=" + cityId + " AND travel_services.origin_id=pools.id";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Pool pool = new Pool(rs.getInt("id"), rs.getString("name"), rs.getString("address"), cityId, rs.getString("phone"));
                pools.add(pool);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return pools;
    }

    public ArrayList<City> getCityServices() {
        ArrayList<City> cities = new ArrayList<City>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT DISTINCT origin.originCityId AS cityId, origin.originCity AS cityName FROM travel_services JOIN (SELECT pools.id As originId, cities.id as originCityId, cities.name as originCity FROM pools,cities WHERE pools.city_id=cities.id) As origin WHERE travel_services.origin_id = origin.originId";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                City city = new City(rs.getInt("cityId"), rs.getString("cityName"));
                cities.add(city);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return cities;
    }

    public ArrayList<Shipping> getServices(int poolFrom, int poolTo) {
        ArrayList<Shipping> shippings = new ArrayList<Shipping>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT shipping_services.id,name,rate FROM shipping_services,shipping_qos WHERE origin_id="+poolFrom+" AND destination_id="+poolTo+" AND qos_id=shipping_qos.id ORDER BY qos_id ASC";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Shipping s = new Shipping();
                s.setId(rs.getInt(1));
                s.setQos(rs.getString(2));
                s.setCost(rs.getInt(3));
                shippings.add(s);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return shippings;
    }

    public void addService(int poolFrom, int poolTo, int qosId, int cost) {
        PreparedStatement stmt = null;

        try {
            String query = "INSERT INTO shipping_services (origin_id, destination_id, qos_id, rate) VALUES (" + poolFrom + ", " + poolTo + "," + qosId + ",'" + cost + "') ";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
            this.lm.log("layanan "+poolFrom+" "+poolTo+" telah ditambahkan");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<ShippingQos> getShippingQos() {
        ArrayList<ShippingQos> sq = new ArrayList<ShippingQos>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM shipping_qos";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                ShippingQos s = new ShippingQos();
                s.setId(rs.getInt(1));
                s.setName(rs.getString(2));
                sq.add(s);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return sq;
    }

    public void deleteService(int serviceId) {
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "DELETE FROM shipping_services WHERE id = " + serviceId;
            stmt.executeUpdate(query);
            this.lm.log("layanan shipping "+serviceId+" telah dihapus");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateService(int serviceId, int poolFrom, int poolTo, int qosId, int cost) {
        PreparedStatement stmt = null;

        try {
            String query = "UPDATE shipping_services SET origin_id=" + poolFrom + ", destination_id=" + poolTo + ", qos_id=" + qosId + ", rate=" + cost + " WHERE id=" + serviceId;
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
            this.lm.log("layanan shippings "+serviceId+" telah diperbaharui");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Shipping getShipping(int serviceId) {
        Shipping s = new Shipping();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM shipping_services WHERE id = " + serviceId;
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            s.setId(rs.getInt(1));
            s.setOriginId(rs.getInt(2));
            s.setDestinationId(rs.getInt(3));
            s.setQosId(rs.getInt(4));
            s.setCost(rs.getInt(5));
            
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return s;
    }

    public ArrayList<Pool> getPoolsFrom(int serviceId) {
        ArrayList<Pool> pools = new ArrayList<Pool>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT origin_id FROM shipping_services WHERE id=" + serviceId;
            ResultSet rs = stmt.executeQuery(query);
            rs.next();

            int poolFrom = rs.getInt(1);

            stmt = con.createStatement();
            query = "SELECT city_id FROM pools WHERE id=" + poolFrom;
            rs = stmt.executeQuery(query);
            rs.next();

            int city = rs.getInt(1);

            stmt = con.createStatement();
            query = "SELECT id,name FROM pools WHERE city_id=" + city;
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                Pool p = new Pool();
                p.setId(rs.getInt(1));
                p.setName(rs.getString(2));
                pools.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return pools;
    }

    public ArrayList<Pool> getPoolsTo(int serviceId) {
        ArrayList<Pool> pools = new ArrayList<Pool>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT destination_id FROM shipping_services WHERE id=" + serviceId;
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            int poolFrom = rs.getInt(1);

            stmt = con.createStatement();
            query = "SELECT city_id FROM pools WHERE id=" + poolFrom;
            rs = stmt.executeQuery(query);
            rs.next();
            int city = rs.getInt(1);

            stmt = con.createStatement();
            query = "SELECT id,name FROM pools WHERE city_id=" + city;
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                Pool p = new Pool();
                p.setId(rs.getInt(1));
                p.setName(rs.getString(2));
                pools.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return pools;
    }

    public ArrayList<Integer> getReport() {
        ArrayList<Integer> result = new ArrayList<Integer>();
        Statement stmt = null;
        int count;
        try {
            Calendar cal = Calendar.getInstance();
            String first,last;
            for(int i=0;i<12;i++){
                cal.set(Calendar.MONTH, i);
                cal.set(Calendar.DATE, 1);
                first = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-01";
                cal.add(Calendar.MONTH, 1);
                cal.add(Calendar.DATE, -1);
                last = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
                
                stmt = con.createStatement();
                String query = "SELECT COUNT(id) FROM shippings WHERE date between '"+first+"' AND '"+last+"'";
                ResultSet rs = stmt.executeQuery(query);
                rs.next();
                count = rs.getInt(1);
                result.add(count);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public ArrayList<Integer> getReport(int year) {
        ArrayList<Integer> result = new ArrayList<Integer>();
        Statement stmt = null;
        int count;
        try {
            Calendar cal = Calendar.getInstance();
            cal.set(Calendar.YEAR, year);
            String first,last;
            for(int i=0;i<12;i++){
                cal.set(Calendar.MONTH, i);
                cal.set(Calendar.DATE, 1);
                first = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-01";
                cal.add(Calendar.MONTH, 1);
                cal.add(Calendar.DATE, -1);
                last = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
                
                stmt = con.createStatement();
                String query = "SELECT COUNT(id) FROM shippings WHERE date between '"+first+"' AND '"+last+"'";
                ResultSet rs = stmt.executeQuery(query);
                rs.next();
                count = rs.getInt(1);
                result.add(count);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    
}
